package com.catt.orderdinner.repository;

import com.catt.orderdinner.dataobject.TbOrder;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;
import java.util.List;
import java.util.Map;

public interface TbOrderRepository extends JpaRepository<TbOrder, String> {
    List<TbOrder> findByUserId (String userId);

    @Query(value = "SELECT p.PROJECT_TYPE,p.PROJECT_NAME,p.PROJECT_MANAGER,SUM(ORDER_AMOUNT) AS AMOUNT " +
            "FROM tb_order o LEFT JOIN tb_project p ON p.project_type = o.project_type WHERE o.UPDATE_TIME LIKE ?1 ",
            countQuery="SELECT COUNT(DISTINCT PROJECT_TYPE)FROM tb_order o WHERE o.UPDATE_TIME LIKE ?1 ",
            nativeQuery = true)
    Page<List<Map>> getMonthlyOrder(String monthly, Pageable pageable);
    @Query(value = "SELECT p.PROJECT_TYPE,p.PROJECT_NAME,p.PROJECT_MANAGER,SUM(ORDER_AMOUNT) AS AMOUNT " +
            "FROM tb_order o LEFT JOIN tb_project p ON p.project_type = o.project_type WHERE o.UPDATE_TIME LIKE ?1 ",
            nativeQuery = true)
    List<Map> getMonthlyOrder(String monthly);
}
